use serde::{Deserialize, Serialize};
use sqlx::sqlite::SqlitePool;
use sqlx::Done;

// use chrono::naive::serde::ts_seconds;
use chrono::prelude::*;

use validator::Validate;

#[derive(sqlx::FromRow, Debug, Deserialize, Serialize)]
pub struct Comment {
    pub id: i64,
    #[serde(skip_serializing)]
    pub hash: String,
    pub url: String,
    pub uname: String,
    #[serde(skip_serializing)]
    pub email: Option<String>,
    pub site: Option<String>,
    pub content: String,
    pub up: Option<i64>,
    pub down: Option<i64>,

    // #[serde(with = "ts_seconds")]
    pub created_at: NaiveDateTime,
    // #[serde(with = "ts_seconds")]
    pub updated_at: NaiveDateTime,
}

#[derive(Debug, Validate, Deserialize, Serialize, Default)]
pub struct PartialComment {
    pub hash: Option<String>,
    #[validate(url(message = "需要链接地址格式"))]
    pub url: Option<String>,
    #[validate(required)]
    #[validate(length(min = 3, max = 10, message = "名称长度限制3-10个字之间"))]
    pub uname: Option<String>,
    #[validate(email(message = "需要邮件格式"))]
    pub email: Option<String>,
    #[validate(url(message = "需要链接地址格式"))]
    pub site: Option<String>,
    #[validate(required)]
    #[validate(length(min = 6, max = 520, message = "文字长度需要在6-520个之间"))]
    pub content: Option<String>,
}

impl PartialComment {
    /// 更新内容
    pub async fn update_by_id(&self, pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
        let s = self.clone();
        let now = Local::now().naive_local();

        let rows_affected = sqlx::query!(
            "update comments set uname=?, email=?, site=?, content=?, updated_at=? where id=?",
            s.uname,
            s.email,
            s.site,
            s.content,
            now,
            id
        )
        .execute(pool)
        .await?
        .rows_affected();

        Ok(rows_affected > 0)
    }

    /// 创建内容
    pub async fn create(&self, pool: &SqlitePool) -> anyhow::Result<i64> {
        // sqlite 。。。
        let mut conn = pool.acquire().await?;
        let now = Local::now().naive_local();

        let s = self.clone();
        let insert_id = sqlx::query!(
            r#"INSERT INTO comments (hash, url, uname, email, site, content, created_at, updated_at) VALUES (?1, ?2, ?3, ?4, ?5,?6,?7,?8)"#,
            s.hash,
            s.url,
            s.uname,
            s.email,
            s.site,
            s.content,
            now,
            now
        )
        .execute(&mut conn)
        .await?
        .last_insert_rowid();
        // .last_insert_id(); // mysql

        Ok(insert_id)
    }
}

impl Comment {
    /// 获取所有的评论列表
    pub async fn all_list(
        pool: &SqlitePool,
        offset: i8,
        limit: i8,
    ) -> anyhow::Result<Vec<Comment>> {
        let list: Vec<Comment> = sqlx::query_as!(
            Comment,
            "SELECT * FROM comments order by id desc limit ?,?",
            offset,
            limit
        )
        .fetch_all(pool)
        .await?;

        Ok(list)
    }

    /// 获取 md5(url) 下的评论列表
    pub async fn url_list(
        pool: &SqlitePool,
        h: String,
        offset: i8,
        limit: i8,
    ) -> anyhow::Result<Vec<Comment>> {
        let list: Vec<Comment> = sqlx::query_as!(
            Comment,
            "SELECT * FROM comments where hash=?1 order by id desc limit ?2,?3",
            h,
            offset,
            limit
        )
        .fetch_all(pool)
        .await?;

        Ok(list)
    }

    /// 根据 ID 寻找单条数据
    pub async fn find_by_id(pool: &SqlitePool, id: i64) -> anyhow::Result<Comment> {
        let one: Comment = sqlx::query_as!(Comment, "SELECT * FROM comments WHERE id = ?", id)
            .fetch_one(pool)
            .await?;

        Ok(one)
    }

    /// 根据 ID 删除
    pub async fn delete_by_id(pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
        let rows_affected: u64 = sqlx::query!("DELETE FROM comments WHERE id = ?", id)
            .execute(pool)
            .await?
            .rows_affected();

        Ok(rows_affected > 0)
    }

    /// 👍 赞
    pub async fn up_by_id(pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
        let rows_affected = sqlx::query!("update comments set up=up+1 where id=?", id)
            .execute(pool)
            .await?
            .rows_affected();

        Ok(rows_affected > 0)
    }

    /// 👎 踩
    pub async fn down_by_id(pool: &SqlitePool, id: i64) -> anyhow::Result<bool> {
        let rows_affected = sqlx::query!("update comments set down=down+1 where id=?", id)
            .execute(pool)
            .await?
            .rows_affected();

        Ok(rows_affected > 0)
    }
}
